﻿using System;
using System.Collections.Generic;
using System.Text;
using DotNet.Model;
using DotNet.Common;
using DotNet.Data.Factory;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace DotNet.Data
{
    public class zhengShuDal
    {
        IDBHelp db = DBHelp.Create();
        Hashtable htSyn = Hashtable.Synchronized(new Hashtable());
        StringBuilder strSql = new StringBuilder();
        #region 是否存在该记录
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int linkId)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select count(1) from zhengShu where linkId=@linkId");
            htSyn.Add("linkId", linkId);
            return db.IsExists(strSql.ToString(), htSyn);
        }
        #endregion

        #region 获得证书信息数据列表
        /// <summary>
        /// 获得证书信息数据列表
        /// </summary>
        /// <returns></returns>
        public IList<zhengShu> GetModel()
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select linkId,ZSClass,ZSName,ZSStartDate,ZSEndDate,ZSCode,ZSOrg,ZSTel,ZSWebsite,ZSPic,ZSDesc,ZSState,ZSDate from zhengShu ");
            return getzhengShusBySql(strSql.ToString());
        }
        #endregion

        #region 根据证书信息号查询实体
        /// <summary>
        /// 根据证书信息号查询实体
        /// </summary>
        /// <param name="linkId"></param>
        /// <returns></returns>
        public zhengShu GetModel(int linkId)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select linkId,ZSClass,ZSName,ZSStartDate,ZSEndDate,ZSCode,ZSOrg,ZSTel,ZSWebsite,ZSPic,ZSDesc,ZSState,ZSDate from zhengShu ");
            strSql.Append(" where linkId=@linkId ");
            htSyn.Add("linkId", linkId);
            DataTable dt = db.GetDataTable(strSql.ToString(), htSyn);
            zhengShu zhengShu = new zhengShu();
            if (dt.Rows.Count>0)
            {
                zhengShu.linkId = Convert.ToInt32(dt.Rows[0]["linkId"]);
                zhengShu.ZSClass = dt.Rows[0]["ZSClass"].ToString();
                zhengShu.ZSName = dt.Rows[0]["ZSName"].ToString();
                zhengShu.ZSStartDate = Convert.ToDateTime(dt.Rows[0]["ZSStartDate"]);
                zhengShu.ZSEndDate = Convert.ToDateTime(dt.Rows[0]["ZSEndDate"].ToString());
                zhengShu.ZSCode = dt.Rows[0]["ZSCode"].ToString();
                zhengShu.ZSOrg = dt.Rows[0]["ZSOrg"].ToString();
                zhengShu.ZSTel = dt.Rows[0]["ZSTel"].ToString();
                zhengShu.ZSWebsite = dt.Rows[0]["ZSWebsite"].ToString();
                zhengShu.ZSPic = dt.Rows[0]["ZSPic"].ToString();
                zhengShu.ZSDesc = dt.Rows[0]["ZSDesc"].ToString();
                zhengShu.ZSState = Convert.ToBoolean(dt.Rows[0]["ZSState"]);
                zhengShu.ZSDate = Convert.ToDateTime(dt.Rows[0]["ZSDate"].ToString());
            }           
            return zhengShu;
        }
        #endregion

        #region 修改证书信息
        /// <summary>
        /// 修改证书信息
        /// </summary>
        /// <param name="zhengShu"></param>
        /// <returns></returns>
        public bool Update(zhengShu zhengShu)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("update zhengShu set ");
            strSql.Append("ZSClass=@ZSClass,");
            strSql.Append("ZSName=@ZSName,");
            strSql.Append("ZSStartDate=@ZSStartDate,");
            strSql.Append("ZSEndDate=@ZSEndDate,");
            strSql.Append("ZSCode=@ZSCode,");
            strSql.Append("ZSOrg=@ZSOrg,");
            strSql.Append("ZSTel=@ZSTel,");
            strSql.Append("ZSWebsite=@ZSWebsite,");
            strSql.Append("ZSPic=@ZSPic,");
            strSql.Append("ZSDesc=@ZSDesc,");
            strSql.Append("ZSState=@ZSState,");
            strSql.Append("ZSDate=@ZSDate");
            strSql.Append(" where linkId=@linkId ");
            htSyn.Add("@ZSClass", zhengShu.ZSClass);
            htSyn.Add("@ZSName", zhengShu.ZSName);
            htSyn.Add("@ZSStartDate", zhengShu.ZSStartDate);
            htSyn.Add("@ZSEndDate", zhengShu.ZSEndDate);
            htSyn.Add("@ZSCode", zhengShu.ZSCode);
            htSyn.Add("@ZSOrg", zhengShu.ZSOrg);
            htSyn.Add("@ZSTel", zhengShu.ZSTel);
            htSyn.Add("@ZSWebsite", zhengShu.ZSWebsite);
            htSyn.Add("@ZSPic", zhengShu.ZSPic);
            htSyn.Add("@ZSDesc", zhengShu.ZSDesc);
            htSyn.Add("@ZSState", zhengShu.ZSState);
            htSyn.Add("@ZSDate", zhengShu.ZSDate);
            htSyn.Add("@linkId", zhengShu.linkId);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 删除证书信息根据证书信息号
        /// <summary>
        /// 删除证书信息根据证书信息号
        /// </summary>
        /// <param name="linkId"></param>
        /// <returns></returns>
        public bool Delete(int linkId)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("delete from zhengShu ");
            strSql.Append(" where linkId=@linkId ");
            htSyn.Add("linkId", linkId);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 添加证书信息
        /// <summary>
        /// 添加证书信息
        /// </summary>
        /// <param name="zhengShu"></param>
        /// <returns></returns>
        public bool Insert(zhengShu zhengShu)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("insert into zhengShu(");
            strSql.Append("ZSClass,ZSName,ZSStartDate,ZSEndDate,ZSCode,ZSOrg,ZSTel,ZSWebsite,ZSPic,ZSDesc,ZSState,ZSDate)");
            strSql.Append(" values (");
            strSql.Append("@ZSClass,@ZSName,@ZSStartDate,@ZSEndDate,@ZSCode,@ZSOrg,@ZSTel,@ZSWebsite,@ZSPic,@ZSDesc,@ZSState,@ZSDate)");            
            htSyn.Add("@ZSClass", zhengShu.ZSClass);
            htSyn.Add("@ZSName", zhengShu.ZSName);
            htSyn.Add("@ZSStartDate", zhengShu.ZSStartDate);
            htSyn.Add("@ZSEndDate", zhengShu.ZSEndDate);
            htSyn.Add("@ZSCode", zhengShu.ZSCode);
            htSyn.Add("@ZSOrg", zhengShu.ZSOrg);
            htSyn.Add("@ZSTel", zhengShu.ZSTel);
            htSyn.Add("@ZSWebsite", zhengShu.ZSWebsite);
            htSyn.Add("@ZSPic", zhengShu.ZSPic);
            htSyn.Add("@ZSDesc", zhengShu.ZSDesc);
            htSyn.Add("@ZSState", zhengShu.ZSState);
            htSyn.Add("@ZSDate", zhengShu.ZSDate);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 根据SQL语句获取集合
        /// <summary>
        ///根据SQL语句获取集合
        /// </summary>
        public IList<zhengShu> getzhengShusBySql(string strSql)
        {
            IList<zhengShu> list = new List<zhengShu>();
            DataTable dt = db.GetDataTable(strSql);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    zhengShu zhengShu = new zhengShu();
                    zhengShu.linkId = Convert.ToInt32(dr["linkId"]);
                    zhengShu.ZSClass = dr["ZSClass"].ToString();
                    zhengShu.ZSName = dr["ZSName"].ToString();
                    zhengShu.ZSStartDate = Convert.ToDateTime(dr["ZSStartDate"]);
                    zhengShu.ZSEndDate = Convert.ToDateTime(dr["ZSEndDate"].ToString());
                    zhengShu.ZSCode = dr["ZSCode"].ToString();
                    zhengShu.ZSOrg = dr["ZSOrg"].ToString();
                    zhengShu.ZSTel = dr["ZSTel"].ToString();
                    zhengShu.ZSWebsite = dr["ZSWebsite"].ToString();
                    zhengShu.ZSPic = dr["ZSPic"].ToString();
                    zhengShu.ZSDesc = dr["ZSDesc"].ToString();
                    zhengShu.ZSState = Convert.ToBoolean(dr["ZSState"]);
                    zhengShu.ZSDate = Convert.ToDateTime(dr["ZSDate"].ToString());
                    list.Add(zhengShu);
                }
            }
            return list;
        }
        #endregion
    }
}
